From f78fd9bd891d03b6722fd7dce1ff856ce7baabb8 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Tue, 4 Aug 2026 20:14:24 +0000 Subject: [PATCH] Document build_sightings() --- doc/src/epilog.inc.m4 | 3 + doc/src/functions.m4 | 1 + doc/src/functions/build_sightings.m4 | 171 +++++++++++++++++++++++++++ 3 files changed, 175 insertions(+) create mode 100644 doc/src/functions/build_sightings.m4 diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 8c35cd8..6100bd6 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -956,6 +956,9 @@ elo_ranks_daily_rst(sdb_male, MT)dnl .. |function_build_arrivals_seq| replace:: :ref:`build_arrivals_seq() ` +.. |function_build_sightings| replace:: + :ref:`build_sightings() ` + .. |function_build_swelling_states| replace:: :ref:`build_swelling_states() ` diff --git a/doc/src/functions.m4 b/doc/src/functions.m4 index 9a6ac12..f209cb4 100644 --- a/doc/src/functions.m4 +++ b/doc/src/functions.m4 @@ -35,6 +35,7 @@ users. :maxdepth: 1 functions/build_arrivals_seq.rst + functions/build_sightings.rst functions/build_swelling_states.rst functions/julian.rst functions/julian_to.rst diff --git a/doc/src/functions/build_sightings.m4 b/doc/src/functions/build_sightings.m4 new file mode 100644 index 0000000..28e3b2c --- /dev/null +++ b/doc/src/functions/build_sightings.m4 @@ -0,0 +1,171 @@ +.. Copyright (C) 2026 The Meme Factory, Inc. www.karlpinc.com + + This program is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License as + published by the Free Software Foundation, either version 3 of the + License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with this program. If not, see . + +.. M4 setup +include(constants.m4)dnl +include(macros.m4)dnl +sdb_rst_quotes(`on')dnl +sdb_generated_rst()dnl + + +.. _function_build_sightings: + +build_sightings() -- Rebuild the |SIGHTINGS| table +-------------------------------------------------- + +Synopsis +```````` + +:: + + build_sightings() INT + build_sightings(animid TEXT) INT + +Input +````` + +animid +'''''' + +A |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| value, designating the +individual which is to have its |SIGHTINGS| rows recomputed. + +This value is not validated. +The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| need not exist; the +function will execute without error. + +The function's return value may be examined to determine whether +changes were made to |SIGHTINGS|. + + +Description +``````````` + +.. |build_sightings_summary| replace:: + Rebuild the |SIGHTINGS| table's rows, either all of them or + those of only one individual. + +|build_sightings_summary| + +When this function is called without an argument, it re-computes the +entire |SIGHTINGS| table. + +When called with a |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| value, +it re-computes the |SIGHTINGS| rows belonging to the given individual. + +Rows may be created in |SIGHTINGS| for any day the individual may be +alive, from earliest possible date of birth through the last day they +were observed. +This means, the database is examined for an individual's presence from +the individual's |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.BDMin| through +their |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DepartDate|, inclusive. + +The |SIGHTING_CONTROLS| table controls which tables are examined to +see if an individual was sighted, the order in which they are +examined, and the criteria that need to be met in order to be added to +the |SIGHTINGS| table. +The matching criteria in the rows of |SIGHTING_CONTROLS| are applied, +in |SIGHTING_CONTROLS|.\ |SIGHTING_CONTROLS.Step| order, until the +given individual is found matching the criteria. +At that point a row for the individual is added to |SIGHTINGS| and the +table(s) and criteria matched is recorded in the |SIGHTINGS|.\ +|SIGHTINGS.Step| column. + +Community Membership +'''''''''''''''''''' + +The |SIGHTINGS|.\ |SIGHTINGS.CommID| column is given the value of the +community id associated with the database row matched. + +Per |SIGHTING_CONTROLS|.\ |SIGHTING_CONTROLS.Source| value, this is +how the community value is obtained: + +``sdb_comm_membs_sc`` + + The |COMM_MEMBS|.\ |COMM_MEMBS.CommID| value is used. + +``sdb_non_brec_sighting_sources_sc`` + + The |NON_BREC_SIGHTING_SOURCES|.\ + |NON_BREC_SIGHTING_SOURCES.CommID| value is used. + +``sdb_roles_sc`` + + The community value used, if SIGHTING_CONTROLS.\ + |SIGHTING_CONTROLS.Behavior| is non-|null| and if such a value + exists, is the value contained in the table holding detail on the + event. + For example, aggression events, which have a ``sdb_aggression`` + |EVENTS|.\ |EVENTS.Behavior| value, are related to the + |AGGRESSIONS| table. + The |AGGRESSIONS| table has an |AGGRESSIONS|.\ |AGGRESSIONS.CommID| + column. + The value of the |AGGRESSIONS|.\ |AGGRESSIONS.CommID| column is + used for aggression events when an aggression event is found and + SIGHTING_CONTROLS.\ |SIGHTING_CONTROLS.Behavior| is + ``sdb_aggression``. + + When SIGHTING_CONTROLS.\ |SIGHTING_CONTROLS.Behavior| is |null|, or + when there is no community value in the detail associated with a + behavior, the |WATCHES|.\ |WATCHES.CommID| value is used. + + The "detail" tables with their own CommID column are: + + +--------------------+-------------------+ + | Behavior code | Table | + +====================+===================+ + | |spx_aggression| | |AGGRESSIONS| | + +--------------------+-------------------+ + | |spx_paper| | |LOCATIONS_PAPER| | + +--------------------+-------------------+ + | |spx_utm| | |LOCATIONS_UTM| | + +--------------------+-------------------+ + | |spx_mating_event| | |MATINGS| | + +--------------------+-------------------+ + | |spx_pg_event| | |PANTGRUNTS| | + +--------------------+-------------------+ + +``sdb_swelling_sources_sc`` + + The |COMM_MEMBS|.\ |COMM_MEMBS.CommID| value is used -- if a row + exists on |COMM_MEMBS| for the given individual on the given date. + + If no such row exists on |COMM_MEMBS|, the special |COMM_IDS| + community value of ``sdb_unknown_comm`` is used. + + +Examples +```````` + +.. code-block:: sql + :caption: + Rebuild SIGHTINGS for the individual with the AnimID of "JANE" + + SELECT build_sightings('JANE'); + + +.. code-block:: sql + :caption: + Rebuild the entire SIGHTINGS table + + SELECT build_sightings(); + + + +Return Value +```````````` + +The function returns the number of rows computed, regardless of +how many rows previously existed in |SIGHTINGS|. -- 2.34.1